﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using F_MODEL;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using Pager;

namespace F_DAL
{
   public class Sendandaddressee
    {
       string str = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
       /// <summary>
       /// 待寄件
       /// </summary>
       /// <param name="origins"></param>
       /// <param name="waybillids"></param>
       /// <param name="index"></param>
       /// <param name="size"></param>
       /// <returns></returns>
        public Pager.Plist<Waybill> getSendlist(string origins,string waybillids,int index,int size)
        {
            
            int pageindex = (index - 1) * size + 1;
            int pagesize = index * size;
            StringBuilder sql = new StringBuilder();
            sql.Append(" select * from (");
            sql.Append("select ROW_NUMBER()over (order  by origin)nid, * from view1");
            sql.Append(" where ");
           
            sql.Append("  waybillStatue=4");
            
            if(origins!="")
            {
                sql.Append(" and origin='"+origins+"'");
            }
           if(waybillids!="0")
           {
               sql.Append(" and waybillId='"+waybillids+"'");
           }

           sql.Append(" )A where nid  between " + pageindex + " and "+pagesize);
           SqlConnection con = new SqlConnection(str);
           SqlCommand comm = new SqlCommand(sql.ToString(),con);
           con.Open();
           SqlDataReader reader = comm.ExecuteReader();
           List<Waybill> list = new List<Waybill>();
            while(reader.Read())
            {
                Waybill w = new Waybill();
                w.waybillId = reader["waybillId"].ToString();
                w.mailId=reader["mailID"].ToString();
                w.origin = reader["origin"].ToString();
                w.destination = reader["destination"].ToString();
                w.originTime = Convert.ToDateTime(reader["originTime"]);
                w.destinationTime = Convert.ToDateTime(reader["originTime"]);
                w.name = reader["name"].ToString();
                w.cnTel = reader["cnTel"].ToString();
                w.waybillStatue = Convert.ToInt32(reader["waybillStatue"]);
                list.Add(w);

            }
            con.Close();
            StringBuilder sql1 = new StringBuilder();
            sql1.Append(" select count(*) from (");
            sql1.Append("select ROW_NUMBER()over (order  by origin)nid, * from view1");
            sql1.Append(" where ");

            sql1.Append(" waybillStatue=4");

            if (origins != "")
            {
                sql1.Append(" and origin='" + origins + "'");
            }
            if (waybillids != "0")
            {
                sql1.Append(" and waybillId='" + waybillids + "'");
            }

            sql1.Append(" )A ");
            SqlConnection con1 = new SqlConnection(str);
            SqlCommand comm1 = new SqlCommand(sql1.ToString(), con1);
            con1.Open();
            int count = Convert.ToInt32(comm1.ExecuteScalar());
            con1.Close();
            Pager.Plist<Waybill> p = new Pager.Plist<Waybill>();
            p.pindex = index;
            p.psize = size;
            p.pcount = (int)Math.Ceiling((double)count / (double)size);
            p.pagelist = list;
            return p;

        }
       /// <summary>
       /// 待收件
       /// </summary>
       /// <param name="destinations"></param>
       /// <param name="waybillids"></param>
       /// <param name="index"></param>
       /// <param name="size"></param>
       /// <returns></returns>
        public Pager.Plist<Waybill> getAddresseelist(string destinations, string waybillids, int index, int size)
        {
            int pageindex = (index - 1) * size + 1;
            int pagesize = index * size;
            StringBuilder sql = new StringBuilder();
            sql.Append(" select * from (");
            sql.Append("select ROW_NUMBER()over (order  by destination)nid, * from view1");
            sql.Append(" where ");

            sql.Append("  waybillStatue=5");

            if (destinations != "")
            {
                sql.Append(" and destination='" + destinations + "'");
            }
            if (waybillids != "0")
            {
                sql.Append(" and waybillId='" + waybillids + "'");
            }

            sql.Append(" )A where nid  between " + pageindex + " and " + pagesize + "  ");
            SqlConnection con = new SqlConnection(str);
            SqlCommand comm = new SqlCommand(sql.ToString(), con);
            con.Open();
            SqlDataReader reader = comm.ExecuteReader();
            List<Waybill> list = new List<Waybill>();
            while (reader.Read())
            {
                Waybill w = new Waybill();
                w.waybillId = reader["waybillId"].ToString();
                w.mailId = reader["mailID"].ToString();
                w.origin = reader["origin"].ToString();
                w.destination = reader["destination"].ToString();
                w.originTime = Convert.ToDateTime(reader["originTime"]);
                w.destinationTime = Convert.ToDateTime(reader["originTime"]);
                w.name = reader["name"].ToString();
                w.cnTel = reader["cnTel"].ToString();
                w.waybillStatue = Convert.ToInt32(reader["waybillStatue"]);
                list.Add(w);

            }
            con.Close();
            StringBuilder sql1 = new StringBuilder();
            sql1.Append(" select count(*) from (");
            sql1.Append("select ROW_NUMBER()over (order  by destination)nid, * from view1");
            sql1.Append(" where ");

            sql1.Append("  waybillStatue=5");

            if (destinations != "")
            {
                sql1.Append(" and destination='" + destinations + "'");
            }
            if (waybillids != "0")
            {
                sql1.Append(" and waybillId='" + waybillids + "'");
            }

            sql1.Append(" )A ");
            SqlConnection con1 = new SqlConnection(str);
            SqlCommand comm1 = new SqlCommand(sql1.ToString(), con1);
            con1.Open();
            int count = Convert.ToInt32(comm1.ExecuteScalar());
            con1.Close();
            Pager.Plist<Waybill> p = new Pager.Plist<Waybill>();
            p.pindex = index;
            p.psize = size;
            p.pcount = (int)Math.Ceiling((double)count / (double)size);
            p.pagelist = list;
            return p;

        }
    }
}
